<?php
namespace app\kaoping\controller;
use app\BaseController;
use think\facade\Db;
use think\facade\View;
class Excel extends First{
	//表格模板下载
	function get_demo(){
		$headArr=array('考生姓名','考生拼音','性别','年龄','校区负责人电话','考生身份证号','考试级别','考试分组','考试编号','考试步伐','成绩','考官签字','考试机构');
		// 表格名称
		$filename = "uplode_demo";
		// 调用导出方法
		$this->getExcel($filename,$headArr,$data=array(),13);
	}

	function getUserList(){
		$where='';
		if (input('stime')!='') {
			$where.='a.time>'.input('stime');
		}
		if (input('etime')!='') {
			if ($where!='') {
				$where.=' and a.time<'.input('etime');
			}else{
				$where.='a.time<'.input('etime');
			}
		}
		//dump(input('type'));
		if (input('type')!==null && input('type')!='') {
			if ($where!='') {
				$where.=' and a.type='.input('type');
			}else{
				$where.='a.type='.input('type');
			}
		}
		$list=Db::table('admin')
			->alias('a')
			->leftjoin('school s','a.id=s.aid')
			->where($where)
			->order('a.id asc')
			->field('a.id,s.name,s.contact,s.tel,s.address,a.name as lname,a.paw,a.time')
			->select()->toarray();
		foreach ($list as $k => $v) {
			$list[$k]['time']=date('Y-m-d H:i:s',$v['time']);
		}
		$headArr=array('编号','机构名称','联系人','联系电话','机构地址','账号','密码','注册时间');
		$filename = "会员列表";
		$this->getExcel($filename,$headArr,$list,7);
	}

	function get_student_card(){
		if(session("?atype")){
			$where='';
			if(session("atype")==1){
				$where.="s.sid=".session('aid');
				if(input('stime')!='' || input('etime')!='' || input('name')!=''){
					if (input('stime')!='') {
						$where.=' and s.time>'.strtotime(input('stime'));
					}
					if (input('etime')!='') {
						$etime=strtotime(input('etime'))+60*60*24;
						$where.=' and s.time<'.$etime;
					}
					if (input('name')!='') {
						$where.=' and (s.name="'.input('name').'" or s.idcard ="'.input('name').'")';
					}
				}else{
					if (input('pid')!='') {
						$where.=' and p.id="'.input('pid').'"';
					}
				}
			}else{
				$where='sch.aid !=""';
				if(input('stime')!='' || input('etime')!='' || input('sname')!='' || input('name')!=''){
					if (input('stime')!='') {
						$where.=' and s.time>'.strtotime(input('stime'));
					}
					if (input('etime')!='') {
						$etime=strtotime(input('etime'))+60*60*24;
						$where.=' and s.time<'.$etime;
					}
					if (input('sname')!='') {
						$sname=urlencode(input('sname'));//dump($sname);dump(strpos($sname,'+'));
						if(strpos($sname,'*')!==false || strpos($sname,'@')!==false || strpos($sname,'-')!==false || strpos($sname,'_')!==false || strpos($sname,'+')!==false || strpos($sname,'.')!==false || strpos($sname,'/')!==false){
							$where.=' and sch.name like "%'.$sname.'%"';
						}else{
							$where.=' and sch.name like "%'.input('sname').'%"';
						}
					}
					if (input('name')!='') {
						$where.=' and (s.name="'.input('name').'" or s.idcard ="'.input('name').'")';
					}
				}
				if (input('sid')!='') {
					$where.=' and s.sid='.input('sid');
				}
				if (input('pid')!='') {
					$where.=' and sc.pid="'.input('pid').'"';
				}
			}
		}else{
			die('请登录');
		}
		
		$data=Db::table('student_card sc')
			->join('student s','sc.sid=s.id')
			->join('school sch','sch.aid=s.sid')
			->leftjoin('kaoguan t','sc.teacher=t.id')
			->leftjoin('pici p','sc.pid=p.id')
			->leftjoin('card c','c.id=sc.cid')
			->field('s.name,s.en_name,s.sex,s.age,s.tel,s.idcard,s.idcard as birth,c.name as cname,sc.groups,sc.num,sc.step,sc.state,sc.score,t.name as tname,sc.school,sch.name as sname,p.name as pname')
			->where($where)
			->order('sc.id asc')
			//->fetchsql()
			->select()->toArray();//dump($where);dump($data);die;
		foreach ($data as $field => $v) {
			$data[$field]['sex']=$v['sex']==1?'男':'女';
			if ($v['score']!='') {
				if ($v['score']=='缺考') {
					$data[$field]['state']='缺考';
				}else{
					$data[$field]['state']='正常';
				}
			}else{
				$data[$field]['state']='待考';
			}
			if($v['birth']!='')
				$data[$field]['birth']=mb_substr($v['birth'],6,4).'-'.mb_substr($v['birth'],10,2).'-'.mb_substr($v['birth'],12,2);
			else
				$data[$field]['birth']='';
		}
		//dump($data);die;
		$headArr=array('考生姓名','考生拼音','性别','年龄','校区负责人电话','考生身份证号','出生日期','考试级别','考试分组','考试编号','考试步伐','状态','成绩','考官签字','机构签字','考试机构','批次');
		//dump($data);die;
		// 表格名称
		$filename = "student_score_list";
		// 调用导出方法
		$this->getExcel($filename,$headArr,$data,17);
	}

	/**
	 * execl数据导出
	 * 应用场景：订单导出
	 * @param string $title 模型名（如Member），用于导出生成文件名的前缀
	 * @param array $cellName 表头及字段名
	 * @param array $data 导出的表数据
	 * 特殊处理：合并单元格需要先对数据进行处理
	 */

	private function getExcel($fileName, $headArr, $data=array(),$num){
		//引入核心文件
		include('./../vendor/phpExcel/Classes/PHPExcel.php');//vendor("PHPExcel.Classes.PHPExcel");
		$date = date("Y_m_d", time());
		$fileName .= "_{$date}.xlsx";

		// 创建PHPExcel对象，注意，不能少了\
		$objPHPExcel = new \PHPExcel();
		$objProps = $objPHPExcel->getProperties()->setCreator("ctos")
				->setLastModifiedBy("ctos")
				->setTitle("Office 2007 XLSX Test Document")
				->setSubject("Office 2007 XLSX Test Document")
				->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
				->setKeywords("office 2007 openxml php")
				->setCategory("Test result file");
		// 设置宽度
		/*for ($alpha=65;$alpha<(65+$num);$alpha++){
			$objPHPExcel->getActiveSheet()->getColumnDimension($alpha)->setAutoSize(true);//单元宽度自适应
		}*/
		// 设置表头
		$key = ord("A");//返回ascii码
		foreach ($headArr as $v) {
			$colum = chr($key);//返回相对应于 ascii 所指定的单个字符
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
			$key += 1;
		}

		$column = 2;
		$objActSheet = $objPHPExcel->getActiveSheet();

		foreach ($data as $key => $rows) { // 行写入
			$span = ord("A");
			foreach ($rows as $keyName => $value) { // 列写入
				$j = chr($span);
				$objActSheet->setCellValue($j . $column, $value."\t");
				$span ++;
			}
			$column ++;
		}
		// 设置编码
		$fileName = iconv("utf-8", "gb2312", $fileName);

		// $objPHPExcel->getActiveSheet()->setTitle('test');
		// 设置活动单指数到第一个表,所以Excel打开这是第一个表
		$objPHPExcel->setActiveSheetIndex(0);
		ob_end_clean(); // 清除缓冲区,避免乱码
		header('Content-Type: application/vnd.ms-excel');
		header("Content-Disposition: attachment;filename=\"$fileName\"");
		header('Cache-Control: max-age=0');

		$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		$objWriter->setPreCalculateFormulas(FALSE);
		//print_r($data);exit;
		$objWriter->save('php://output'); // 文件通过浏览器下载
		exit();
	}

	/**
	 * 批量导入地址
	 * @param  [type] $file [导入文件]
	 * @param  [type] $data [字段对应表头]
	 * @param  [type] $Db::table   [数据表]
	 * @param  string $sql  [需要修改时添加该字段]
	 * @param  string $aid  [操作人id]
	 * @param  array  $arr  [附加参数]
	 * @return [type]       [200]
	 */
	protected function excel_add($file,$data,$db='',$sql='',$aid='',$arr=array()){
		// 导入excel插件
		Vendor('PHPExcel.Classes.PHPExcel');
		Vendor('PHPExcel.Classes.PHPExcel.IOFactory');
		Vendor('PHPExcel.Classes.PHPExcel.Reader.Excel5');
		//$objbzy = new\PHPExcel_IOFactory();$objReader = $objbzy->createReader('excel2007'); //use Excel5 for 2003 format
		if(empty($file['tmp_name'])){
			echo "请选择要上传的文件！";exit(0);
		}else{
			// 判断上传文件的类型
			$picname = $file['name'];
			if ($picname != "") {
				$type = strstr($picname, '.');
				if ($type != ".xls" && $type != ".xlsx") {
					echo "请上传正确的文件类型！";exit(0);
				}
			}
			// 判断上传文件的大小
			$picsize = $file['size'];
			if($picsize>5242880){
				echo "上传的文件超过大小限制！";exit(0);//5M
			}
		}
		$objbzy = new\PHPExcel_IOFactory();//$objReader = $objbzy->createReader('excel5'); //use Excel5 for 2003 format
		if ($type=='.xls') {
			$objReader = $objbzy->createReader('Excel5'); //use Excel5 for 2003 format
		}elseif ($type=='.xlsx') {
			$objReader = $objbzy->createReader('Excel2007'); //use Excel5 for 2003 format
		}else{
			return 400;
		}
		$excelpath=$file['tmp_name'];
		$objPHPExcel = $objReader->load($excelpath);
		$sheet = $objPHPExcel->getSheet(0);
		$highestRow = $sheet->getHighestRow();           //取得总行数
		$highestColumn = $sheet->getHighestColumn(); //取得总列数
		//return $highestRow;
		for($j=2;$j<=$highestRow;$j++){               //从第二行开始读取数据
			$str="";
			for($k='A';$k<=$highestColumn;$k++){           //从A列读取数据
				$str .=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().'|*|';//读取单元格
			}
			$str=mb_convert_encoding($str,'utf8','auto');//根据自己编码修改
			$strs = explode("|*|",$str);//dump($data);dump($strs);die;
			foreach($data as $k=>$v){
				$info[$v]=trim($strs[$k]);
			}
			$teacher=Db::table('kaoguan')->where('name',$info['teacher'])->find();
			$student=Db::table('student')->where('idcard',$info['idcard'])->find();
			$card=Db::table('card')->where('name',$info['cid'])->find();
			$pici=Db::table('pici')->where('name="'.$info['pici'].'" and aid='.$student['sid'])->find();
			$res=Db::table('student_card')->where('sid='.$student['id'].' and cid='.$card['id'].' and pid='.$pici['id'])->find();
			$ret=Db::table('student_card')->where('id',$res['id'])->update(['score'=>$info['scores'],'teacher'=>$teacher['id']]);
			if($res===false){
				return 400;//echo 'excel error';exit();
			}
		}
		return 200;
	}
	
	//自定义修改学员成绩
	public function update_score(){
		if (request()->isPost()) {
			$data=array('idcard','cid','scores','teacher','school','pici');//,'score','aid'
			//身份证号,考试级别,老师,机构,批次
			$res=$this->excel_add($_FILES['file'],$data);
			//dump($res);die;
			if($res==200){
				return redirect('/kaoping');
			}else{
				die($res);
			}
		}
	}

	//考试学员批量添加
	public function student_addAll(){
		if (request()->isPost()) {
			$data=array('name','en_name','sex','age','tel','idcard','cid','group','num','step','sname','pici');//,'score','aid'
			//考生姓名,拼音,性别,年龄,手机号,身份证号,考试级别,考试分组,考试编号,考试步伐,考试机构,批次
			$arr['time']=time();//dump($_FILES);die;
			$res=$this->student_add($_FILES['file'],$data,$db='student',$sql='',$aid='',$arr);
			//dump($res);die;
			if($res==200){
				return redirect('index/student_list');
			}else{
				die($res);
			}
		}
	}

	/**
	 * 批量导入地址
	 * @param  [type] $file [导入文件]
	 * @param  [type] $data [字段对应表头]
	 * @param  [type] $Db::table   [数据表]
	 * @param  string $sql  [需要修改时添加该字段]
	 * @param  string $aid  [操作人id]
	 * @param  array  $arr  [附加参数]
	 * @return [type]       [200]
	 */
	protected function student_add($file,$data,$db,$sql='',$aid='',$arr=array()){
		// 导入excel插件
		Vendor('PHPExcel.Classes.PHPExcel');
		Vendor('PHPExcel.Classes.PHPExcel.IOFactory');
		Vendor('PHPExcel.Classes.PHPExcel.Reader.Excel5');
		//$objbzy = new\PHPExcel_IOFactory();$objReader = $objbzy->createReader('excel2007'); //use Excel5 for 2003 format
		if(empty($file['tmp_name'])){
			echo "请选择要上传的文件！";return 400;
		}else{
			// 判断上传文件的类型
			$picname = $file['name'];
			if ($picname != "") {
				$type = strstr($picname, '.');
				if ($type != ".xls" && $type != ".xlsx") {
					echo "请选择正确的文件类型！";exit(0);
				}
			}
			// 判断上传文件的大小
			$picsize = $file['size'];
			if($picsize>5242880){
				echo "上传的文件超过大小限制！";exit(0);//5M
			}
		}
		$objbzy = new \PHPExcel_IOFactory();//$objReader = $objbzy->createReader('excel5'); //use Excel5 for 2003 format
		if ($type=='.xls') {
			$objReader = $objbzy->createReader('Excel5'); //use Excel5 for 2003 format
		}elseif ($type=='.xlsx') {
			$objReader = $objbzy->createReader('Excel2007'); //use Excel5 for 2003 format
		}else{
			return 400;
		}
		$excelpath=$file['tmp_name'];
		$objPHPExcel = $objReader->load($excelpath);
		$sheet = $objPHPExcel->getSheet(0);
		$highestRow = $sheet->getHighestRow();//halt($highestRow);           //取得总行数
		/*if($highestRow > 200)
			die('表格单次上传数据不得超过200条，如有大量数据，请多次上传!');*/
		$highestColumn = $sheet->getHighestColumn(); //die($highestColumn);//取得总列数
		for($j=2;$j<=$highestRow;$j++){               //从第二行开始读取数据
			$str="";
			for($k='A';$k<=$highestColumn;$k++){           //从A列读取数据
				$str .=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().'|*|';//读取单元格
			}
			$str=mb_convert_encoding($str,'utf8','auto');//根据自己编码修改
			$strs = explode("|*|",$str);
			$info=[];
			foreach($data as $k=>$v){
				if($strs[$k]!=''){
					$info[$v]=trim($strs[$k]);
				}
			}
			if(!empty($info)){
				$exam=array();$stu=array();
				$stu['name']=$info['name'];
				$stu['en_name']=$info['en_name'];
				if($stu['en_name']==''){
				    $stu['en_name']=get_pinyin($stu['name']);
				}
				$stu['idcard']=$info['idcard'];
				str_replace(' ','',$info['sex'])=='男'?$stu['sex']=1:'';
				str_replace(' ','',$info['sex'])=='女'?$stu['sex']=2:'';
				if($info['idcard']!=''){
					$birth_yer=mb_substr($info['idcard'],6,4);
					$stu['age']=date('Y')-$birth_yer;
				}else{
					$stu['age']=0;
				}
				$stu['tel']=$info['tel'];
				if(session('atype')==1){
					$stu['sid']=session('aid');//return 1234;
				}else{
					$s=Db::table('school')->where('aid!="" and name="'.str_replace(' ','',$info['sname']).'"')->find();//dump($info);dump($s);die;
					if ($s) {
						$stu['sid']=$s['aid'];
					}else{
						return '未找到机构信息';
					}
				}
				if($info['pici']!=''){
					$pici=Db::table('pici')->where('name="'.$info['pici'].'" and aid='.$stu['sid'])->find();//dump($info);dump($pici);die;//查找批次是否存在
					if($pici){
						$exam['pid']=$pici['id'];
					}else{
						return '未找到对应批次信息';
					}
				}else{
					return '请填写批次信息';
				}
				$card=Db::table('card')->where('name',trim($info['cid']))->find();//dump($card);dump($info);die;//查询考证等级是否存在
				if ($card) {
					$exam['cid']=$card['id'];
				}else{
					return '未找到考证等级信息';
				}
				/*if (trim($info['aid'])!='') {
					$aid=Db::table('admin')->where('real_name="'.str_replace(' ','',$info['aid']).'" and type=2')->find();//dump($aid);die;//查询考官是否存在
					if ($aid) {
						$exam['aid']=$aid['id'];
					}else{
						continue;
					}
				}*/
				$student=Db::table('student')->where('idcard',$info['idcard'])->find();//dump($stu);die;//查找是否有该学生信息
				if ($student) {
					$stu['id']=$student['id'];
					@Db::table('student')->update($stu);
					$exam['sid']=$student['id'];//return $student;
				}else{
					$stu['time']=time();//dump($stu);die;
					$ss=Db::table('student')->insertGetid($stu);
					if ($ss) {
						$exam['sid']=$ss;
					}else{
						continue;
					}
				}
				$exam['groups']=$info['group'];
				$exam['num']=$info['num'];
				$exam['step']=$info['step'];
				//$exam['score']=$info['score'];//dump($exam);die;
				//return $exam;
				$ex=Db::table('student_card')->where('sid='.$exam['sid'].' and cid='.$exam['cid'].' and pid='.$exam['pid'])->find();//查找是否有报考记录
				if ($ex) {
					$exam['id']=$ex['id'];
					$res=Db::table('student_card')->update($exam);
				}else{
					$exam['time']=time();
					$res=Db::table('student_card')->insert($exam);
				}
				if($res===false){
					return 400;//echo 'excel error';exit();
				}
			}
		}
		return 200;
	}
}